﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Earthwatchers.Models;
using System.Data.SqlClient;
using Dapper;

namespace Earthwatchers.Data
{
    public class EarthwatcherRepository : IEarthwatcherRepository
    {
        private readonly IDbConnection connection;

        public EarthwatcherRepository(string connectionString)
        {
            connection = new SqlConnection(connectionString);
        }

        public Earthwatcher GetEarthwatcher(int id)
        {
            connection.Open();
            var earthwatchers = connection.Query<Earthwatcher>("select a.Id as Id,a.EarthwatcherGuid as Guid, a.Name as Name,a.Role as Role, l.Id as LandId from Earthwatcher a left join Land l on a.EarthwatcherGuid=l.EarthwatcherGuid where a.Id=@Id", new { Id = id });
            connection.Close();
            return earthwatchers.FirstOrDefault();
        }

        public Earthwatcher GetEarthwatcher(String name)
        {
            connection.Open();
            var earthwatchers = connection.Query<Earthwatcher>("select a.Id as Id,a.EarthwatcherGuid as Guid, a.Name as Name,a.Role as Role, l.Id as LandId from Earthwatcher a left join Land l on a.EarthwatcherGuid=l.EarthwatcherGuid where a.Name=@Name", new { Name = name });
            connection.Close();
            return earthwatchers.FirstOrDefault();
        }

        public Earthwatcher GetEarthwatcher(Guid guid)
        {
            connection.Open();
            var earthwatchers = connection.Query<Earthwatcher>("select a.Id as Id,a.EarthwatcherGuid as Guid, a.Name as Name,a.Role as Role,l.Id as LandId from Earthwatcher a left join Land l on a.EarthwatcherGuid=l.EarthwatcherGuid where a.EarthwatcherGuid=@Guid", new { Guid = guid });
            connection.Close();
            return earthwatchers.FirstOrDefault();
        }

        public Earthwatcher CreateEarthwatcher(Earthwatcher earthwatcher)
        {
            connection.Open();
            var cmd=connection.CreateCommand();
            cmd.CommandText = "insert into Earthwatcher(EarthwatcherGuid, Name, Role) values(@guid,@name,@role)";
            cmd.Parameters.Add(new SqlParameter("@guid", earthwatcher.Guid));
            cmd.Parameters.Add(new SqlParameter("@name", earthwatcher.Name));
            cmd.Parameters.Add(new SqlParameter("@role", earthwatcher.Role));
            cmd.ExecuteNonQuery();
            connection.Close();
            return earthwatcher;
        }

        public void UpdateEarthwatcher(int id, Earthwatcher earthwatcher)
        {
            connection.Open();
            var cmd = connection.CreateCommand();
            cmd.CommandText = "update Earthwatcher set role=@role where id=@id";
            cmd.Parameters.Add(new SqlParameter("@role", earthwatcher.Role));
            cmd.Parameters.Add(new SqlParameter("@id", earthwatcher.Id));
            cmd.ExecuteNonQuery();
            connection.Close();
        }

        public void DeleteEarthwatcher(int id)
        {
            DeleteConfirmations(id);
            DeleteDeconfirmations(id);
            DeleteComments(id);
            connection.Open();
            connection.Execute("delete from Earthwatcher where id=@id", new {id });
            connection.Close();
        }

        public void DeleteConfirmations(int id)
        {
            connection.Open();
            connection.Execute("delete from DeforestationConfirmers where Earthwatcher=@id", new { id });
            connection.Close();
        }

        public void DeleteDeconfirmations(int id)
        {
            connection.Open();
            connection.Execute("delete from DeforestationDeconfirmers where Earthwatcher=@id", new { id });
            connection.Close();
        }

        public void DeleteComments(int id)
        {
            connection.Open();
            connection.Execute("delete from comments where EarthwatcherId=@id", new { id });
            connection.Close();
        }

        public List<Earthwatcher> GetAllEarthwatchers()
        {
            connection.Open();
            var earthwatchers = connection.Query<Earthwatcher>("select Id, EarthwatcherGuid as Guid, Name, Role from Earthwatcher");
            connection.Close();
            return earthwatchers.ToList();
        }

        public string AssignLandToEarthwatcher(string earthwatcherGuid, string basecamp)
        {
            var geohex= GetFreeLand(basecamp);
            if (geohex != null)
            {
                var landRepository = new LandRepository(connection.ConnectionString);
                landRepository.UpdateLandEarthwatcherguid(geohex, earthwatcherGuid);
            }
            return geohex;
        }

        /**
        public string AssignLandToEarthwatcher(string earthwatcherGuid, string basecamp)
        {
            var basecamp1 = basecamp ?? "borneo";
            connection.Open();
            var command = new SqlCommand("AssignLandToEarthwatcher", (SqlConnection)connection);
            command.CommandType=CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@EarthwatcherGuid", earthwatcherGuid);
            var p=command.Parameters.Add("@GeohexKey", SqlDbType.NVarChar, 11);
            p.Direction = ParameterDirection.Output;
            command.ExecuteNonQuery();
            connection.Close();
            var result = command.Parameters["@GeohexKey"].Value;
            if (result != DBNull.Value)
            {
                return (string)result;
            }
            else
            {
                return null;
            }

        }
        */

        private string GetFreeLand(string baseCamp)
        {
            string geohexKey = null;
            var sql= @"select top 1 l.Centroid.STDistance(e.shape) as distance1, l.GeohexKey  
                from earthwatchers.Land l, earthwatchers.basecamp e 
                where e.name=@basecamp
                and l.EarthwatcherGuid IS NULL
                and l.LandStatus > 0
                and l.Landthreat > 0 
                order by distance1 asc";

            connection.Open();
            var cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(new SqlParameter("@basecamp", baseCamp));
            var reader= cmd.ExecuteReader();
            var hasResult=reader.Read();
            if (hasResult)
            {
                geohexKey = reader.GetString(1);
            }
            connection.Close();
            return geohexKey;
        }
    }
}
